from sqlalchemy import Column, Integer, String, ForeignKey, Table, text
from sqlalchemy.ext.declarative import declarative_base

from conf.mysql_cnx import MysqlCnxPool
from service.user import User

Base = declarative_base()


class Report(Base):
    __tablename__ = 'report'
    id = Column(Integer, primary_key=True)

    finding = Column(String)
    impression = Column(String)
    label_ids = Column(String)
    status = Column(Integer)
    comment = Column(String)


class UserReport(Base):
    __tablename__ = 'user_report'
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer)
    report_id = Column(Integer, primary_key=True)


class ReportStatus:
    SPARE = 0
    ASSIGNED = 1
    COMPLETED = 2


def find_user_report_list(user_id, page_size, page_num):
    conn = MysqlCnxPool().get_conn()
    sql = text('SELECT '
               'report.id, '
               'report.finding, '
               'report.comment, '
               'report.status, '
               'report.update_time '
               'FROM user '
               'JOIN user_report ON user.id = user_report.user_id AND user.id = :user_id '
               'JOIN report ON report.id = user_report.report_id '
               'ORDER BY report.update_time DESC, id ASC '
               'LIMIT :offset , :limit')

    data_list = conn.execute(sql, {'user_id': user_id,
                                   'offset': (page_num - 1) * page_size,
                                   'limit': page_size}).fetchall()
    sql = text('SELECT '
               'COUNT(*) '
               'FROM user '
               'JOIN user_report ON user.id = user_report.user_id AND user.id = :user_id '
               'JOIN report ON report.id = user_report.report_id ')

    total = conn.execute(sql, {'user_id': user_id}).scalar()
    return data_list, total


def find_next_spare_report(user_id):
    conn = MysqlCnxPool().get_conn()
    sql = text('SELECT '
               'report.id, '
               'report.finding, '
               'report.impression, '
               'report.comment, '
               'report.label_ids '
               'FROM user '
               'JOIN user_report ON user.id = user_report.user_id AND user.id = :user_id '
               'JOIN report ON report.id = user_report.report_id AND report.status != :status '
               'ORDER BY report.update_time DESC, id ASC '
               'LIMIT 0, 1')
    report = conn.execute(sql, {'user_id': user_id, 'status': ReportStatus.COMPLETED}).fetchone()
    return report


def assign_reports_to_user(username, quantity):
    session = MysqlCnxPool().get_session()
    user = session.query(User).filter(User.username == username).first()
    reports = session.query(Report).filter(Report.status == ReportStatus.SPARE).limit(quantity).all()
    for report in reports:
        session.add(UserReport(user_id=user.id, report_id=report.id))
        report.status = ReportStatus.ASSIGNED
    session.commit()


if __name__ == '__main__':
    find_user_report_list(1020, 3, 1)

    find_next_spare_report(1020)

    assign_reports_to_user(username='wzj', quantity=100)

    session = MysqlCnxPool().get_session()

    results = session.query(User).filter(User.username == 'wzj').first()
    print(results)
